package org.iteam.xlz.core.service.product;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.iteam.commons.utils.DateUtil;
import org.iteam.core.utils.SqlBuilder;
import org.iteam.db.BaseService;
import org.iteam.xlz.core.entity.product.ProductSale;
import org.iteam.xlz.core.entity.product.ProductTags;

public class ProductSaleService extends BaseService<ProductSale>{
	static ProductSaleService instance;
	private List<Object> params=new ArrayList<Object>();
	private StringBuilder sql=null;
	
	private ProductSaleService(){}
	
	public static ProductSaleService getInstance(){
		if(null==instance){
			instance=new ProductSaleService();
		}
		return instance;
	}
	public long counts(String _productname,String ptid,String parent,String leftCreateTime,String rightCreateTime){
		try {
			params.clear();
			sql=new StringBuilder("select count(l.ppid) from productputaway l left join product r on l.pid=r.pid where 1=1");
			if(StringUtils.isNotBlank(_productname)){
				sql.append(" and r.productname like ?");
				params.add("%"+_productname+"%");
			}
			if(StringUtils.isNotBlank(parent)&&StringUtils.isBlank(ptid)){
				sql.append(" and r.ptid in(select * from producttype where typeparent=?)");
				params.add(parent);
			}
			if(StringUtils.isNotBlank(ptid)){
				sql.append(" and r.ptid =?");
				params.add(ptid);
			}
			if(StringUtils.isNotBlank(leftCreateTime)&&StringUtils.isNotBlank(rightCreateTime)){
				sql.append(" and l.productuptime<? and l.productdowntime>?");
				params.add(leftCreateTime);
				params.add(rightCreateTime);
			}
			return super.counts(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public ProductSale findOne(String ppid){
		try {
			return super.show("select * from productputaway where 1=1 and ppid=?",ppid);
		} catch (SQLException e) {
			e.printStackTrace();
			return new ProductSale();
		}
	}
	
	public String validate(String ppid,String pid,String beginTime,String endTime){
		try {
			if(org.iteam.core.utils.DateUtil.parseDateTime(beginTime).getTime()>org.iteam.core.utils.DateUtil.parseDateTime(endTime).getTime()){
				return "日期不合法";
			}
			params.clear();
			sql=new StringBuilder("select * from productputaway where 1=1 and ?<=productdowntime and ?>=productuptime and pid=?");
			params.add(beginTime);
			params.add(endTime);
			params.add(pid);
			if(StringUtils.isNotBlank(ppid)){
				sql.append(" and ppid <>?");
				params.add(ppid);
			}
			sql.append(" limit 1");			
			ProductSale ProductSale=super.show(sql.toString(),params.toArray());
			if(null==ProductSale){
				return null;
			}
			return "已有一条记录日期为："+ProductSale.getProductuptime()+"到"+ProductSale.getProductdowntime();
		} catch (SQLException e) {
			e.printStackTrace();
			return "出错了，请联系管理员";
		} catch (ParseException e) {
			e.printStackTrace();
			return "出错了，请联系管理员";
		}
	}
	//库存商品，商品编号，商品类型编号，商品父类型，上架时间
	public List<ProductSale> query(String _productname,String ptid,String parent,String leftCreateTime,String rightCreateTime,long page,long rows){
		try {
			params.clear();
			sql=new StringBuilder("select l.* from productputaway l left join product r on l.pid=r.pid where 1=1");
			if(StringUtils.isNotBlank(_productname)){
				sql.append(" and r.productname like ?");
				params.add("%"+_productname+"%");
			}
			if(StringUtils.isNotBlank(parent)&&StringUtils.isBlank(ptid)){
				sql.append(" and r.ptid in(select * from producttype where typeparent=?)");
				params.add(parent);
			}
			if(StringUtils.isNotBlank(ptid)){
				sql.append(" and r.ptid =?");
				params.add(ptid);
			}
			if(StringUtils.isNotBlank(leftCreateTime)&&StringUtils.isNotBlank(rightCreateTime)){
				sql.append(" and l.productuptime<? and l.productdowntime>?");
				params.add(leftCreateTime);
				params.add(rightCreateTime);
			}
			sql.append(" order by l.createtime desc limit ?,?");
			params.add((page-1)*rows);
			params.add(rows);
			return super.query(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<ProductSale>();
		}
	}
	
	public boolean create(ProductSale model,String ticket){
		Connection con=null;
		try {
			con=super.getConnection(false);
			SqlBuilder sql=SqlBuilder.createInsert("productputaway");
			String ppid=UUID.randomUUID().toString().replace("-","").toUpperCase();
			sql.addField("productname",model.getProductname());
			sql.addField("productprice",model.getProductprice());
			sql.addField("productshowprice",model.getProductshowprice());
			sql.addField("os",model.getOs());
			sql.addField("productuptime",model.getProductuptime());
			sql.addField("productdowntime",model.getProductdowntime());
			sql.addField("hascommission",model.getHascommission());
			sql.addField("reducednumber",model.getReducednumber());
			sql.addField("pid",model.getPid());
			sql.addField("ppid",ppid);
			sql.addField("createtime",DateUtil.currentTime());
			sql.addField("creator",model.getCreator());
			sql.addField("show_order",model.getShow_order());
			int i=super.updates(con,sql.getSql(),sql.getParams());
			//商品职位商家
			SqlBuilder tSql=SqlBuilder.createUpdate("ticketinfo");
			tSql.addField("ppid",ppid);
			tSql.addField("pid",model.getPid());
			tSql.addWhere("tid",ticket);
			int j=super.updates(con,tSql.getSql(),tSql.getParams());
			int k=super.updates(con,"update product set productstatus=0 where pid=?",model.getPid());
			if(i==1&&j==1&&k==1){
				super.commit(con);
				return true;
			}else{
				super.rollback(con);
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				super.rollback(con);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		return false;
	}
	
	public boolean update(ProductSale model){
			SqlBuilder sql=SqlBuilder.createUpdate("productputaway");
			sql.addField("productname",model.getProductname());
			sql.addField("productprice",model.getProductprice());
			sql.addField("productshowprice",model.getProductshowprice());
			sql.addField("os",model.getOs());
			sql.addField("productuptime",model.getProductuptime());
			sql.addField("productdowntime",model.getProductdowntime());
			sql.addField("hascommission",model.getHascommission());
			sql.addField("reducednumber",model.getReducednumber());
			sql.addField("show_order",model.getShow_order());
			sql.addWhere("ppid",model.getPpid());	
			try {
				return super.updates(sql.getSql(),sql.getParams())>0;
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
	}
	
	public List<ProductSale> getUnTicketProduct(int tickettype){
		params.clear();
		sql=new StringBuilder("select l.* from productputaway l left join product r on l.pid=r.pid left join ticketinfo t on l.ppid=t.ppid where 1=1 and r.status=0");
		sql.append(" and t.ppid is null and t.tickettype=?");
		params.add(tickettype);
		try {
			return super.query(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<ProductSale>();
		}
	}
	
	public List<ProductSale> getOnSaleProduct(String leftCreateTime,String rightCreateTime){
		try {
			if(StringUtils.isBlank(leftCreateTime)||StringUtils.isBlank(rightCreateTime)){
				Calendar c=Calendar.getInstance();
				rightCreateTime=DateUtil.currentTime();
				c.set(Calendar.DAY_OF_MONTH,1);
				c.set(Calendar.HOUR_OF_DAY,0);
				c.set(Calendar.MINUTE,0);
				c.set(Calendar.SECOND,0);
				leftCreateTime=org.iteam.core.utils.DateUtil.formatDateTime(c.getTime());
			}
			return super.query("select * from productputaway where productuptime<? and productdowntime>?",rightCreateTime,leftCreateTime);
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<ProductSale>();
		}
	}
}		